<?php

	class Base_Model_Sales extends Model_Base{

		public static $liveCycle = [];

		CONST SOLDS_TYPE = [1 => 7, 2 => 15, 3 => 30];
		/**
		 * 获取子仓销量查询列表
		 * @param array $params
		 */
		public function getSonSaleList(array $params) {
			$catelogModel = new Application_Model_ProductCatalog();
			$where = $init = ' where 1 ';
			$filter = array();
			if ($params['warehouse_code'] != '') {
				$where .= ' and pss.warehouse_code = :wcode';
				$filter['wcode'] = $params['warehouse_code'];
			}
			if ($params['product_type'] != '') {
				$categorys = array_column($catelogModel->getAllChildCatalog($params['product_type'],['id']), 'id');
				$where .= $this->db()->quoteInto(' and pc.id in (?)', $categorys);
			}
			if ($params['lifecycle'] != '') {
				$where .= ' and pws.life_cycle = :life_cycle';
				$filter['life_cycle'] = $params['lifecycle'];
			}
			if ($params['selected_product_type'] != '' && $params['searchbox'] != '') {
				if ($params['selected_product_type'] == 1) {
					$where .= $this->db()->quoteInto(" and pss.goods_sn in (?)", explode(',', $params['searchbox']));
				} else {
					$where .= ' and p.product_name like :product_name';
					$filter['product_name'] = '%'.$params['searchbox'].'%';
				}
			}
			if ($params['sale_type'] != '') {
				$sale_type = '';
				switch ($params['sale_type']) {
					case 'minus_7':
						$sale_type = '(real_sale_7-sale_7)';
						break;
					case 'minus_15':
						$sale_type = '(real_sale_15-sale_15)';
						break;
					case 'minus_30':
						$sale_type = '(real_sale_30-sale_30)';
						break;
					default:
						$sale_type = '';
				}
				if ($sale_type != '') {
					if ($params['minus_from'] != '' && $params['minus_to'] != '') {
						$where .= ' and '. $sale_type . ' between :start and :end';
						$filter['start'] = $params['minus_from'];
						$filter['end'] = $params['minus_to'];
					} elseif ($params['minus_from'] != '') {
						$where .= ' and '. $sale_type .' >= :start';
						$filter['start'] = $params['minus_from'];
					} elseif ($params['minus_to'] != '') {
						$where .= ' and '. $sale_type .' <= :end';
						$filter['end'] = $params['minus_to'];
					}
				}
			}
			$sql = 'select %field% 
					from p_product_sales_statistics pss 
					left join c_warehouse cw on pss.warehouse_code = cw.warehouse_code 
					left join p_product_warehouse_stock pws on pss.goods_sn = pws.goods_sn AND pws.warehouse_code = pss.warehouse_code 
					left join p_product p on pss.goods_sn = p.goods_sn 
					left join p_product_catalog pc ON p.catalog_id = pc.id';
			$field = "pss.*, p.thumb_url, pc.name_ch cateName,cw.warehouse_name, p.product_name, pws.life_cycle, pws.base_stock";
			$query_sql = str_replace('%field%', $field, $sql);
			$query_sql .= $where . " order by pss.id limit ". ($params['page']-1) * $params['perPage'] .",".$params['perPage'];
			$countSql = str_replace('%field%', 'count(*) as total', $sql) . $where;
			$data = [];
			if ($where != $init) {
				$data['list'] = $this->db()->fetchAll($query_sql, $filter);
				$data['count'] = $this->db()->fetchOne($countSql, $filter);
			} else {
				$data['list'] = [];
				$data['count'] = 0;
			}
			return $data;
		}
		public function getSumSaleList(array $params) {
			$catelogModel = new Application_Model_ProductCatalog();
			$where = $init = ' where 1 ';
			$filter = array();
			if ($params['product_type'] != '') {
				$categorys = array_column($catelogModel->getAllChildCatalog($params['product_type'],['id']), 'id');
				$where .= $this->db()->quoteInto(' and pc.id in (?)', $categorys);
			}
			if ($params['selected_product_type'] != '' && $params['searchbox'] != '') {
				if ($params['selected_product_type'] == 1) {
					$where .= $this->db()->quoteInto(" and pss.goods_sn in (?)", explode(',', $params['searchbox']));
				} else {
					$where .= ' and p.product_name like :product_name';
					$filter['product_name'] = '%'.$params['searchbox'].'%';
				}
			}
			$sql = "select %field% 
					from p_product_sales_statistics pss 
					left join c_warehouse cw on pss.warehouse_code = cw.warehouse_code 
					left join p_product_warehouse_stock pws on pss.goods_sn = pws.goods_sn AND pws.warehouse_code = pss.warehouse_code 
					left join p_product p on pss.goods_sn = p.goods_sn 
					left join p_product_catalog pc ON p.catalog_id = pc.id";
			$field = "p.thumb_url, pc.name_ch cateName,p.product_name, pss.goods_sn,sum(if((cw.type ='4' || cw.type='5'),0,pws.base_stock)) as sum_base_stock,
					sum(if((cw.type ='4' || cw.type='5'),0,real_sale_7)) as sum_real_sale_7,
					sum(if((cw.type ='4' || cw.type='5'),0,real_sale_15)) as sum_real_sale_15,
					sum(if((cw.type ='4' || cw.type='5'),0,real_sale_30)) as sum_real_sale_30";
			$query_sql = str_replace('%field%', $field, $sql);
			$query_sql .= $where . " group by pss.goods_sn order by pss.id limit ". ($params['page']-1) * $params['perPage'] .",".$params['perPage'];
			$countSql = str_replace('%field%', 'count(*) as total', $sql) . $where ." group by pss.goods_sn";
			$countSql = "select count(*) as total from (".$countSql.") as temps";
			$data = [];
			if ($where != $init) {
				$data['list'] = $this->db()->fetchAll($query_sql, $filter);
				$data['count'] = $this->db()->fetchOne($countSql, $filter);
			} else {
				$data['list'] = [];
				$data['count'] = 0;
			}
			
			return $data;
		}
	}